from flask import Blueprint, render_template, request, redirect, url_for, flash
from database import get_db

# 系数管理专用Blueprint
coefficient_bp = Blueprint('coefficient', __name__, url_prefix='/coefficients')

@coefficient_bp.route('/')
def coefficient_list():
    db = get_db()
    search = request.args.get('search', '')
    
    query = '''
    SELECT c.*, cat.name as category_name, 
           CONCAT_WS('->', 
             (SELECT name FROM coefficient_categories WHERE id = cat.parent_id), 
             cat.name) as category_path 
    FROM carbon_coefficients c 
    JOIN coefficient_categories cat ON c.category_id = cat.id
    '''
    params = []
    
    if search:
        query += ''' WHERE c.name LIKE ? OR c.gas LIKE ? OR c.source LIKE ? OR 
                          cat.name LIKE ?'''
        search_param = '%' + search + '%'
        params = [search_param, search_param, search_param, search_param]
    
    coefficients = db.execute(query, params).fetchall()
    return render_template('coefficients.html', coefficients=coefficients)

@coefficient_bp.route('/add', methods=['GET', 'POST'])
def add_coefficient():
    db = get_db()
    if request.method == 'POST':
        name = request.form['name']
        gas = request.form['gas']
        value = request.form['value']
        unit = request.form['unit']
        source = request.form['source']
        category_id = request.form['category_id']
        
        db.execute(
            'INSERT INTO carbon_coefficients (name, gas, value, unit, source, category_id) VALUES (?, ?, ?, ?, ?, ?)',
            (name, gas, value, unit, source, category_id)
        )
        db.commit()
        flash('系数添加成功', 'success')
        return redirect(url_for('coefficient.coefficient_list'))
    
    categories = db.execute(
        '''WITH RECURSIVE category_hierarchy AS (
               SELECT id, name, parent_id, 
                      CAST(id AS TEXT) AS hierarchy_path, 
                      1 AS level
               FROM coefficient_categories
               WHERE parent_id = 0 OR parent_id IS NULL
               UNION ALL
               SELECT cc.id, cc.name, cc.parent_id, 
                      ch.hierarchy_path || '.' || printf('%03d', cc.id) AS hierarchy_path, 
                      ch.level + 1 AS level
               FROM coefficient_categories cc
               JOIN category_hierarchy ch ON cc.parent_id = ch.id
           )
           SELECT cc.id, cc.name, cc.level, cc.parent_id, 
                  COALESCE(p.name, '无') as parent_name, 
                  COUNT(c.id) as child_count, ch.hierarchy_path
           FROM coefficient_categories cc
           LEFT JOIN coefficient_categories c ON cc.id = c.parent_id
           LEFT JOIN coefficient_categories p ON cc.parent_id = p.id
           LEFT JOIN category_hierarchy ch ON cc.id = ch.id
           GROUP BY cc.id, cc.name, cc.level, cc.parent_id, parent_name, ch.hierarchy_path
           ORDER BY CASE WHEN ch.hierarchy_path IS NOT NULL THEN ch.hierarchy_path ELSE CAST(cc.id AS TEXT) END'''
    ).fetchall()
    # 修改模板路径，移除多余的 coefficient/ 前缀
    return render_template('add_coefficient.html', categories=categories)

@coefficient_bp.route('/<int:id>/edit', methods=['GET', 'POST'])
def edit_coefficient(id):
    db = get_db()
    coefficient = db.execute('SELECT * FROM carbon_coefficients WHERE id = ?', (id,)).fetchone()
    
    if coefficient is None:
        flash('碳排放系数不存在', 'danger')
        return redirect(url_for('coefficient.coefficient_list'))
    
    if request.method == 'POST':
        name = request.form['name']
        gas = request.form['gas']
        value = request.form['value']
        unit = request.form['unit']
        source = request.form['source']
        category_id = request.form['category_id']
        
        db.execute(
            'UPDATE carbon_coefficients SET name = ?, gas = ?, value = ?, unit = ?, source = ?, category_id = ? WHERE id = ?',
            (name, gas, value, unit, source, category_id, id)
        )
        db.commit()
        flash('系数更新成功', 'success')
        return redirect(url_for('coefficient.coefficient_list'))
    
    # 修改分类查询逻辑，与add_coefficient保持一致
    categories = db.execute(
        '''WITH RECURSIVE category_hierarchy AS (
               SELECT id, name, parent_id, 
                      CAST(id AS TEXT) AS hierarchy_path, 
                      1 AS level
               FROM coefficient_categories
               WHERE parent_id = 0 OR parent_id IS NULL
               UNION ALL
               SELECT cc.id, cc.name, cc.parent_id, 
                      ch.hierarchy_path || '.' || printf('%03d', cc.id) AS hierarchy_path, 
                      ch.level + 1 AS level
               FROM coefficient_categories cc
               JOIN category_hierarchy ch ON cc.parent_id = ch.id
           )
           SELECT cc.id, cc.name, cc.level, cc.parent_id, 
                  COALESCE(p.name, '无') as parent_name, 
                  COUNT(c.id) as child_count, ch.hierarchy_path
           FROM coefficient_categories cc
           LEFT JOIN coefficient_categories c ON cc.id = c.parent_id
           LEFT JOIN coefficient_categories p ON cc.parent_id = p.id
           LEFT JOIN category_hierarchy ch ON cc.id = ch.id
           GROUP BY cc.id, cc.name, cc.level, cc.parent_id, parent_name, ch.hierarchy_path
           ORDER BY CASE WHEN ch.hierarchy_path IS NOT NULL THEN ch.hierarchy_path ELSE CAST(cc.id AS TEXT) END'''
    ).fetchall()
    return render_template('edit_coefficient.html', coefficient=coefficient, categories=categories)

@coefficient_bp.route('/<int:id>/delete', methods=['POST'])
def delete_coefficient(id):
    db = get_db()
    coefficient = db.execute('SELECT * FROM carbon_coefficients WHERE id = ?', (id,)).fetchone()
    
    if coefficient is None:
        flash('碳排放系数不存在', 'danger')
    else:
        db.execute('DELETE FROM carbon_coefficients WHERE id = ?', (id,))
        db.commit()
        flash('系数删除成功', 'success')
    return redirect(url_for('coefficient.coefficient_list'))